package example;

import db.domain.Org;
import db.domain.Sex;
import db.domain.StudentScore;
import db.domain.User;
import db.domain.UserMeta;
import org.junit.BeforeClass;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.junit.runners.JUnit4;
import sf.database.dialect.DialectUtils;
import sf.database.jdbc.sql.SQLContext;
import sf.database.jdbc.sql.SQLParameter;
import sf.database.meta.EntityUtils;
import sf.dsl.example.Example;
import sf.dsl.example.ExampleSQL;
import sf.dsl.example.Order;
import sf.dsl.example.QPI;
import sf.dsl.example.RawValue;
import sf.dsl.example.SimpleField;
import sf.dsl.example.SelectOpt;
import sf.dsl.example.SimpleTable;
import sf.dsl.example.WrapperSimpleTable;

import java.util.ArrayList;
import java.util.List;

@RunWith(JUnit4.class)
public class ExampleTest2 {

    @BeforeClass
    public static void one() {
        SimpleTable c1 = new SimpleTable(User.class);
        SimpleTable c2 = new SimpleTable(UserMeta.class);
        SimpleTable c3 = new SimpleTable(StudentScore.class);
    }

    @Test
    public void main() {
        System.out.println(ExampleTest2.class.getName().replace(".", "/"));


        Example e = new Example();
        SimpleTable c = new SimpleTable(User.class);
        e.where().trueCondition().and().w(c.c(User.Field.id).eq(1))
                .and().leftP()
                .w(c.c(User.Field.sex).isNotNull()).or().w(c.c(User.Field.displayName).isNull())
                .rightP();
        e.orderBy(c.c(User.Field.id), Order.DESC).orderBy(c.c(User.Field.displayName), Order.DESC);
        List<SimpleTable> list = e.getFromTables();
        StringBuilder sb = new StringBuilder();
        List<SQLParameter> parameters = new ArrayList<>();
        ExampleSQL.getSQLContextWhereClause(sb, parameters, e);

        System.out.println(sb);
        StringBuilder sb1 = new StringBuilder();
        ExampleSQL.getOrderByClause(sb1, null, e.getOrderByList(), e.getMustTableAlias(), e.getDialect());
        System.out.println(sb1);

        Example e2 = new Example();
        SimpleTable c2 = new SimpleTable(User.class);
        e2.select(c2.c(User.Field.id).as("kpl")).from(c2).groupBy(c2.c(User.Field.sex).as("oii")).having(c2.c(User.Field.age).as("uuuu").between(20, 50));
        SQLContext context = ExampleSQL.getSelectSQLContext(e2, null, SelectOpt.all, false);
        System.out.println(context.getSql());
        System.out.println(context.getParas());

        e2 = new Example();
        e2.setDialect(DialectUtils.mysql);
        c2 = new SimpleTable(User.class);
        e2.select(c2.allFields()).from(c2).offsetLimit(50, 20).orderByAsc(c2.c(User.Field.id));
        context = ExampleSQL.getSelectSQLContext(e2, null, SelectOpt.all, true);
        System.out.println(context.getSql());

    }


    @Test
    public void test1() {
        String key = EntityUtils.c(User.Field.activationKey);
        System.out.println(key);
        key = User.Field.activationKey.c();
        System.out.println(key);
    }

    @Test
    public void test2() {

        SimpleTable USER1 = new SimpleTable(User.class).as("u");
        SimpleTable USERMETA = new SimpleTable(UserMeta.class).as("m");
        Example e = new Example();
        e.select(
                        USER1.c(User.Field.id).as("tid"),
                        USERMETA.c(UserMeta.Field.key).as("key")
                )
                .from(USER1, USERMETA).where().w(USER1.c(User.Field.id).eq(USERMETA.c(UserMeta.Field.userId)));

        SQLContext context = ExampleSQL.getSelectSQLContext(e, null, SelectOpt.all, false);
        System.out.println(context.getSql());

        e = new Example(DialectUtils.mysql);
        SimpleTable USER2 = new SimpleTable(User.class);
        e.select(
                        USER1.c(User.Field.loginName).as("order")
                )
                .from(USER1).where()
                .w(USER1.c(User.Field.sex).as("kkkk").eq(Sex.Male)).and().w(USER1.c(User.Field.description).like("23%", true)).and()
                .exists(
                        new Example().select("1").from(USER2).where().w(USER2.c(User.Field.id).eq(2323))
                );

        context = ExampleSQL.getSelectSQLContext(e, null, SelectOpt.all, false);
        System.out.println(context.getSql());
        System.out.println(context.getParas());
    }

    @Test
    public void testJoinOn() {
        SimpleTable c1 = new SimpleTable(User.class).as("a");
        SimpleTable c2 = new SimpleTable(UserMeta.class).as("b");
        SimpleTable c3 = new SimpleTable(UserMeta.class).as("c");
        Example e = new Example();
        e.select(c1.all()).from(c1)
                .leftJoin(c2, QPI.on(), c1.c(User.Field.id).eq(c2.c(UserMeta.Field.userId)))
                .innerJoin(c3, QPI.on(), c1.c(User.Field.id).eq(c3.c(UserMeta.Field.userId)), QPI.and(), c1.c(User.Field.id).eq(222));
        SQLContext context = ExampleSQL.getSelectSQLContext(e, null, SelectOpt.all, false);
        System.out.println(context.getSql());
        System.out.println(context.getParas());
        e = new Example();
        e.select(c1.all()).from(c1)
                .innerJoin(QPI.wrappedTable(new Example().select(c2.all()).from(c2).where(c2.c(UserMeta.Field.id).ge(3))).as("c"),
                        QPI.on(), new SimpleField("c", "user_id", null).eq(c1.c(User.Field.id)), QPI.and(), c1.c(User.Field.id).eq(1))
                .where().w(c1.c(User.Field.age).ge(100));
        context = ExampleSQL.getSelectSQLContext(e, null, SelectOpt.all, false);
        System.out.println(context.getSql());
        System.out.println(context.getParas());

        e = new Example();
        e.setDialect(DialectUtils.mysql).select(c1.all()).from(c1)
                .leftJoin(c2, QPI.using(User.Field.id))
                .innerJoin(c3, QPI.using(User.Field.id));
        context = ExampleSQL.getSelectSQLContext(e, null, SelectOpt.all, false);
        System.out.println(context.getSql());
        System.out.println(context.getParas());
    }

    @Test
    public void test4() {
        SimpleTable c1 = new SimpleTable(User.class);
        SimpleTable c2 = new SimpleTable(UserMeta.class);
        Example e = new Example();
        e.select(c1.all()).from(c1)
                .where()
                .w(c1.c(User.Field.id).ge(QPI.any(
                                new Example().select(c2.c(UserMeta.Field.userId)).from(c2).where(c2.c(UserMeta.Field.id).ge(100))
                        ))
                ).and().w(c1.c(User.Field.age).eq(new RawValue("20")));
        SQLContext context = ExampleSQL.getSelectSQLContext(e, null, SelectOpt.all, false);
        System.out.println(context.getSql());
        System.out.println(context.getParas());
    }

    @Test
    public void test5() {
        SimpleTable c1 = new SimpleTable(User.class);
        Example e = new Example();
        e.select(c1.all()).from(c1)
                .where(c1.c(User.Field.id).ge(100), QPI.and(),
                        QPI.leftP(),
                        c1.c(User.Field.sex).eq(1), QPI.or(), c1.c(User.Field.sex).eq(2),
                        QPI.rightP(),
                        QPI.or(),
                        QPI.leftP(),
                        c1.c(User.Field.weight).in(1, 2, 3), QPI.and(), c1.c(User.Field.nicename).like("123%"),
                        QPI.rightP()
                );
        SQLContext context = ExampleSQL.getSelectSQLContext(e, null, SelectOpt.all, false);
        System.out.println(context.getSql());
        System.out.println(context.getParas());
        e = new Example();
        e.select(c1.all()).from(c1)
                .where().w(c1.c(User.Field.id).ge(100)).and()
                .leftP()
                .w(c1.c(User.Field.sex).eq(1)).or().w(c1.c(User.Field.sex).eq(2))
                .rightP().or().leftP()
                .w(c1.c(User.Field.weight).in(1, 2, 3)).and().w(c1.c(User.Field.weight).notIn(6, 7, 8)).and().w(c1.c(User.Field.maps1).like("123%")).rightP();
        context = ExampleSQL.getSelectSQLContext(e, null, SelectOpt.all, false);
        System.out.println(context.getSql());
        System.out.println(context.getParas());

        e = new Example(User.class);
        e.createCriteria().and().ge(User.Field.id, 100).and().leftP().eq(User.Field.sex, 1)
                .or().eq(User.Field.sex, 2).rightP().or().leftP()/*.eq(UserMeta.Field.userId,1).and()*/
                .in(User.Field.weight, 1, 2, 3).and().notIn(User.Field.weight, 6, 7, 8).and().like(User.Field.maps1, "123%").rightP()
                .and().between(User.Field.id, 1, 3);
        context = ExampleSQL.getSelectSQLContext(e, null, SelectOpt.all, false);
        System.out.println(context.getSql());
        System.out.println(context.getParas());
    }

    @Test
    public void test6() {
        SimpleTable c1 = new SimpleTable(User.class).as("u");
        SimpleTable c2 = new SimpleTable(UserMeta.class);
        Example e = new Example();
        e.select(QPI.max(c1.c(User.Field.sex)), QPI.fixColumn("1").as("wd")).from(c1)
                .where().w(c1.c(User.Field.id).in(new Example().select(c2.c(UserMeta.Field.userId)).from(c2).where(c2.c(UserMeta.Field.id).gt(2))));
        SQLContext context = ExampleSQL.getSelectSQLContext(e, null, SelectOpt.all, false);
        System.out.println(context.getSql());
        System.out.println(context.getParas());
    }

    @Test
    public void test7() {
        SimpleTable c1 = new SimpleTable(User.class).as("u");
        SimpleTable c2 = new SimpleTable(UserMeta.class).as("m");
        Example e = new Example();
        e.select(new SimpleField("t", "id", null)).from(
                        new WrapperSimpleTable(new Example().select(c1.allFields()).from(c1).where().w(c1.c(User.Field.id).ge(2))).as("t")
                        , c2)
                .where().w(c2.c(UserMeta.Field.userId).eq(new SimpleField("t", "id", null)))
                .and().createCriteria().eq(c2.c(UserMeta.Field.userId), new SimpleField("t", "id", null));
        SQLContext context = ExampleSQL.getSelectSQLContext(e, null, SelectOpt.all, false);
        System.out.println(context.getSql());
        System.out.println(context.getParas());
    }

    @Test
    public void test8() {
        SimpleTable c1 = new SimpleTable(User.class);
        SimpleTable c2 = new SimpleTable(UserMeta.class);
        Example e = new Example();
        e.select(c1.c(User.Field.id)).from(c1).orderByDesc(c1.c(User.Field.id))
                .union(new Example().select(c2.c(UserMeta.Field.userId)).from(c2).w(c2.c(UserMeta.Field.id).eq(2)))
                .unionAll(new Example().select(c2.c(UserMeta.Field.userId)).from(c2).w(c2.c(UserMeta.Field.id).eq(3)));
        SQLContext context = ExampleSQL.getSelectSQLContext(e, null, SelectOpt.all, false);
        System.out.println(context.getSql());
        System.out.println(context.getParas());
    }

    /**
     *
     */
    @Test
    public void test9() {
        SimpleTable t1 = new SimpleTable(StudentScore.class).as("order");
        Example e = new Example();
        e.setDialect(DialectUtils.mysql);

        e.select(t1.c(StudentScore.Field.schoolTerm),
                        t1.c(StudentScore.Field.subject),
                        QPI.count(t1.c(StudentScore.Field.score)).as("count"),
                        QPI.min(t1.c(StudentScore.Field.score)).as("min_score"),
                        QPI.max(t1.c(StudentScore.Field.score)).as("max_score"),
                        QPI.avg(t1.c(StudentScore.Field.score)).as("avg_score")
                ).from(t1)
//                .where()//构造where条件
//                .w(t1.c(StudentScore.Field.schoolTerm).ge(2000))
//                .and().w(t1.c(StudentScore.Field.subject).in("英语", "数学", "语文"))
//                .and().w(t1.c(StudentScore.Field.score).ge(60))
//                .and().w(t1.c(StudentScore.Field.deleted).eq(false))

                .createCriteria()//构造where条件
                .ge(t1.c(StudentScore.Field.schoolTerm), 2000)
                .and().in(t1.c(StudentScore.Field.subject), "英语", "数学", "语文")
                .and().ge(t1.c(StudentScore.Field.score), 60)
                .and().eq(t1.c(StudentScore.Field.deleted), false).backMain(Example.class)

                .groupBy(t1.c(StudentScore.Field.schoolTerm), t1.c(StudentScore.Field.subject))
                .having(QPI.count(t1.c(StudentScore.Field.score)).gt(1))
                .orderByAsc(t1.c(StudentScore.Field.schoolTerm), t1.c(StudentScore.Field.subject));

        SQLContext context = ExampleSQL.getSelectSQLContext(e, null, SelectOpt.all, true);
        System.out.println(context.getSql());
        System.out.println(context.getParas());

        context = ExampleSQL.getSelectSQLContext(e, null, SelectOpt.page, true);
        System.out.println(context.getListSql());
        System.out.println(context.getCountSql());
        System.out.println(context.getParas());
    }

    @Test
    public void testXX() {
        SQLContext context = null;
        SimpleTable c1 = new SimpleTable(User.class);
        Example e = new Example();

        e.select(c1.all()).from(c1).createCriteria()
                .ge(User.Field.maps1, 1)
                .and().like(User.Field.maps1, "123%", '/');
        context = ExampleSQL.getSelectSQLContext(e, null, SelectOpt.all, false);

        long l = System.currentTimeMillis();
        for (int i = 0; i < 100000; i++) {
            e = new Example();
            e.setDialect(DialectUtils.mysql);
            e.select(c1.all()).from(c1).where().w(c1.c(User.Field.maps1).ge(1))
                    .and().w(c1.c(User.Field.maps1).like("123%", '/'))
                    .and().w(c1.c(User.Field.age).in(1, 2, 3, 4));
            context = ExampleSQL.getSelectSQLContext(e, null, SelectOpt.all, false);

        }
        System.out.println(System.currentTimeMillis() - l);

        e = new Example();
        e.setDialect(DialectUtils.mysql);
        e.select(c1.all()).from(c1).where().w(c1.c(User.Field.maps1).ge(1))
                .and().w(c1.c(User.Field.maps1).like("123%", '/'))
                .and().w(c1.c(User.Field.age).in(1, 2, 3, 4));
        l = System.currentTimeMillis();
        for (int i = 0; i < 100000; i++) {
            e = new Example();
            e.setDialect(DialectUtils.mysql);
            e.select(c1.all()).from(c1).createCriteria()
                    .ge(User.Field.maps1, 1)
                    .and().like(User.Field.maps1, "123%", '/').and().in(c1.c(User.Field.age), new Object[]{1, 2, 3, 4});
            context = ExampleSQL.getSelectSQLContext(e, null, SelectOpt.all, false);
        }
        System.out.println(System.currentTimeMillis() - l);


        System.out.println(context.getSql());
        System.out.println(context.getParas());
    }

    @Test
    public void test10() {
        System.out.println(ExampleTest2.class.getName().replace(".", "/"));

        Example example = new Example(User.class);
        example.createCriteria().and().eq(User.Field.id, 1).and().leftP().isNotNull(User.Field.sex).or().isNull(User.Field.displayName).rightP();
        example.orderBy(QPI.column(User.Field.id), Order.DESC).orderBy(QPI.column(User.Field.id), Order.ASC).orderBy(QPI.column(User.Field.displayName), Order.DESC);


        StringBuilder sb = new StringBuilder();
        List<SQLParameter> list = new ArrayList<>();
        ExampleSQL.getSQLContextWhereClause(sb, list, example);

        System.out.println(sb);
        StringBuilder sb1 = new StringBuilder();
        ExampleSQL.getOrderByClause(sb1, null, example.getOrderByList(), example.getMustTableAlias(), example.getDialect());
        System.out.println(sb1);
        example.select(QPI.count(QPI.distinct(QPI.column(User.Field.id))), QPI.max("a"), QPI.func("REPLACE", QPI.column(User.Field.activationKey), QPI.column(User.Field.sex), QPI.fixColumn("'1233'")));
        SQLContext context = ExampleSQL.getSelectSQLContext(example, null, null, true);
        System.out.println(context.getSql());
        System.out.println(context.getParas());

        Example e2 = new Example(User.class);
        e2.selectProperties(User.Field.id).w(QPI.addCondition(" age = "), QPI.add(36)).orderByAsc(QPI.column(User.Field.id)).createCriteria()
                .or().eq(User.Field.id, 1);
        sb = new StringBuilder();
        list = new ArrayList<>();
        ExampleSQL.getSQLContextWhereClause(sb, list, e2);
        System.out.println(sb);
        System.out.println(list);
    }

    @Test
    public void testWithOnly() {
        SimpleTable c1 = new SimpleTable(User.class);
        Example e = new Example();
        e.with("a1", null, new Example().select(c1.c(User.Field.id)).from(c1).where().w(c1.c(User.Field.id).eq(1))
                .unionAll(new Example().select(c1.c(User.Field.id)).from(c1).where().w(c1.c(User.Field.id).eq(3)))
        ).select(new SimpleField("id")).from(new SimpleTable("a1"));
        SQLContext context = ExampleSQL.getSelectSQLContext(e, null, SelectOpt.all, false);
        System.out.println(context.getSql());
        System.out.println(context.getParas());

        e = new Example();
        e.with("a1", new SimpleField[]{new SimpleField("id"),
                        new SimpleField("age")}, new Example().select(c1.c(User.Field.id)).from(c1).where().w(c1.c(User.Field.id).eq(1)))
                .with("a2", new SimpleField[]{new SimpleField("id"),
                        new SimpleField("age")}, new Example().select(c1.c(User.Field.id)).from(c1).where().w(c1.c(User.Field.id).eq(3))
                ).select(new SimpleField("id")).from(new SimpleTable("a1"));
        context = ExampleSQL.getSelectSQLContext(e, null, SelectOpt.all, false);
        System.out.println(context.getSql());
        System.out.println(context.getParas());

        SimpleTable t1 = new SimpleTable(Org.class);
        SimpleTable t2 = new SimpleTable(Org.class);
        e = new Example();
        e.withRecursive("res", null, new Example().select(t1.all()).from(t1).where().w(t1.c(Org.Field.id).eq(1))
                .union(new Example().select(t2.all()).from(t2.as("t2"))
                        .innerJoin(new SimpleTable("res").as("t3"), QPI.on(),
                                t2.c(Org.Field.id).eq(new SimpleField("t3", "parent_id", null))))
        ).select(QPI.all().tableAlias("res")).from(new SimpleTable("res"));
        context = ExampleSQL.getSelectSQLContext(e, null, SelectOpt.all, false);
        System.out.println(context.getSql());
        System.out.println(context.getParas());
    }
}
